/*
* Script para crear la base de datos de Mas Vida Ministry Manager
*/
USE master
GO
IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'MinistryManagerDB')
	DROP DATABASE MinistryManagerDB
GO

CREATE DATABASE MinistryManagerDB

GO

USE MinistryManagerDB

GO

-- Roles table
CREATE TABLE [Role](
	RoleId				INT PRIMARY KEY IDENTITY,
	Name				VARCHAR (100) NOT NULL UNIQUE
)

INSERT INTO Role Values('Administrator'), ('Normal')

-- Volunteers table
CREATE TABLE Volunteer(
	VolunteerId			INT PRIMARY KEY IDENTITY,
	RoleId				INT NOT NULL,
	Name				VARCHAR(150) NOT NULL,
	Password			VARCHAR(150) NOT NULL,
	Email				VARCHAR(150) NOT NULL UNIQUE,
	IsActive			BIT	NULL,
	CreatedDate			DATETIME NOT NULL
)

ALTER TABLE Volunteer ADD CONSTRAINT Fk_VolunteerRole FOREIGN KEY (RoleId) REFERENCES [Role](RoleId)

INSERT INTO Volunteer (RoleId, Name, Password, Email, IsActive, CreatedDate) VALUES (1, 'Ruben','123', 'rubencg88@gmail.com', 1, GETDATE())

-- Ministry table
CREATE TABLE Ministry(
	MinistryId			INT PRIMARY KEY IDENTITY,
	Name				VARCHAR(150) NOT NULL,
	IsActive			BIT	NULL,
	CreatedDate			DATETIME NOT NULL
)

INSERT INTO Ministry (Name, IsActive, CreatedDate) VALUES ('Multimedia', 1, GETDATE())

-- Service day table
CREATE TABLE ServiceDay(
	ServiceDayId		INT PRIMARY KEY IDENTITY,
	MinistryId			INT NOT NULL,
	ServiceDate			DATETIME NOT NULL,
	Note				VARCHAR(MAX) NULL
)

ALTER TABLE ServiceDay ADD CONSTRAINT Fk_ServiceDayMinistry FOREIGN KEY (MinistryId) REFERENCES Ministry(MinistryId)

-- Service Day Volunteers table
CREATE TABLE ServiceVolunteer(
	ServiceVolunteerId	INT PRIMARY KEY IDENTITY,
	ServiceDayId		INT NOT NULL,
	VolunteerId			INT NOT NULL
)

ALTER TABLE ServiceVolunteer ADD CONSTRAINT Fk_ServiceVolunteerServiceDay FOREIGN KEY (ServiceDayId) REFERENCES ServiceDay(ServiceDayId)
ALTER TABLE ServiceVolunteer ADD CONSTRAINT Fk_ServiceVolunteerVolunteer FOREIGN KEY (VolunteerId) REFERENCES Volunteer(VolunteerId)

-- Service tasks table
CREATE TABLE ServiceTask(
	ServiceTaskId		INT PRIMARY KEY IDENTITY,

)